// 引入 express 框架
const express = require('express')
const mysql = require("mysql");
// 创建实例
const app = express();
const bodyParser = require('body-parser');
// app.use(bodyParser.json());
// app.use(bodyParser.urlencoded({ extended: true }));
// 修改bodyParser接收的最大值
app.use(bodyParser.json({limit:'500mb'}));
app.use(bodyParser.urlencoded({ limit:'500mb', extended: true }));

app.all('*', function (req, res, next) {
    res.header('Access-Control-Allow-Origin', '*');
    //*表示可以跨域任何域名都行（包括直接存在本地的html文件）出于安全考虑最好只设置 你信任的来源也可以填域名表示只接受某个域名
    res.header('Access-Control-Allow-Headers', 'X-Requested-With,Content-Type');
    //可以支持的消息首部列表
    res.header('Access-Control-Allow-Methods', 'PUT,POST,GET,DELETE,OPTIONS');
    //可以支持的提交方式
    res.header('Content-Type', 'application/json;charset=utf-8');//响应头中定义的类型
    next();
});

const connection = mysql.createConnection({//建立一个连接参数方法
    host: 'localhost', //数据库地址
    port: '3306',//端口号
    user: 'root',//用户名
    password: '123456',//密码
    database: 'orangecat'//数据库名称
});

//用参数与数据库进行连接
connection.connect((err) => {
    if (err) throw err;
    console.log('Connected!');
});

// let sql = 'SELECT * FROM users';
let str = '';

// 登录
app.get('/login', function (req, res) {
    let username = req.query.username
    let password = req.query.password
    // console.log(username);
    // console.log(password);
    // "select * from users where username='rui' and password='123456'";
    let sql = `select * from users where username="${username}" and password="${password}"`
    connection.query(
        sql, function (err, result) {
            if (err) {
                console.log('[SELECT ERROR]：', err.message);
            }
            // res.cookie("name",'zhangsan',{maxAge: 900000, httpOnly: true});
            res.send(result);

        })
})

// 注册
app.get('/reg', function (req, res) {
    let username = req.query.username
    let password = req.query.password
    // console.log(username);
    // console.log(password);
    let sql = `insert into users(username,password) values ("${username}","${password}")`;
    connection.query(
        sql, function (err, result) {
            if (err) {
                console.log('[INSERT ERROR]：', err.message);
                // 插入重复时报错，返回405状态，让前端捕获到错误
                res.status(405);
                res.send(err);
            }
            // res.send(result);
            let user_id = result.insertId;
            let sql2 =
                `INSERT INTO navArray (user_id,page, icon)
            VALUES 
            (${user_id}, "主页", "icon-zhuye"),
            (${user_id}, "学习", "icon-xuexi"),
            (${user_id}, "工具", "icon-changyonggongju2"),
            (${user_id}, "娱乐", "icon-yuleAPP"),
            (${user_id}, "视频", "icon-shipin"),
            (${user_id}, "程序员", "icon-daima"),
            (${user_id}, "购物", "icon-gouwuchexuanzhong");`
            connection.query(
                sql2, function (err, result) {
                    if (err) {
                        console.log('[SELECT ERROR]：', err.message);
                    }
                    // res.cookie("name",'zhangsan',{maxAge: 900000, httpOnly: true});
                    // res.send(result);
                    // console.log(result);
                });
            let sql3 =
            `INSERT INTO linkTotalArray (user_id,page,url,linkName,icon,color,img)
            VALUES 
            (${user_id}, "主页","http://www.bilibili.com/","哔哩哔哩", "#icon-bilibili1","",""),
            (${user_id}, "主页","https://www.douyin.com/","抖音", "#icon-douyin","",""),
            (${user_id}, "主页","https://www.taobao.com/","淘宝", "#icon-taobao","",""),
            (${user_id}, "主页","https://www.jd.com/","京东", "#icon-jingdong","",""),
            (${user_id}, "主页","https://m.weibo.cn/","微博", "#icon-weibo","",""),
            (${user_id}, "主页","https://music.163.com/","网易云音乐", "#icon-wangyiyun","",""),
            (${user_id}, "主页","https://fanyi.baidu.com/","百度翻译", "#icon-baidufanyi","",""),
            (${user_id}, "主页","https://i.mooc.chaoxing.com/","学习通", "#icon-chaoxing","",""),
            (${user_id}, "学习","https://i.mooc.chaoxing.com/","学习通", "#icon-chaoxing","",""),
            (${user_id}, "学习","https://www.icourse163.org/","中国大学MOOC", "#icon-zhongguodaxueMOOC","",""),
            (${user_id}, "学习","https://www.yuketang.cn/web","雨课堂", "#icon--yuketang","",""),
            (${user_id}, "学习","https://changjiang.yuketang.cn/web","长江雨课堂", "#icon--yuketang-copy","",""),
            (${user_id}, "学习","https://www.xuetangx.com/","学堂在线", "#icon-tubiaozhizuomoban","",""),
            (${user_id}, "学习","https://baike.baidu.com/","百度百科", "#icon-baidubaike","",""),
            (${user_id}, "学习","https://zh.wikipedia.org/zh/%E7%BB%B4%E5%9F%BA%E7%99%BE%E7%A7%91","维基百科", "#icon-weijibaike","",""),
            (${user_id}, "学习","https://www.cnki.net/","中国知网", "#icon-zhiwang","",""),
            (${user_id}, "学习","https://sci-hub.et-fine.com/","Sci-Hub", "#icon-crown-key","",""),
            (${user_id}, "工具","https://mail.qq.com","QQ邮箱", "#icon-QQyouxiang","",""),
            (${user_id}, "工具","http://pan.baidu.com/","百度网盘", "#icon-baiduyunpan","",""),
            (${user_id}, "工具","https://fanyi.baidu.com/","百度翻译", "#icon-baidufanyi","",""),
            (${user_id}, "工具","https://fanyi.youdao.com/index.html#/","有道 
            翻译", "#icon-youdaofanyi","",""),
            (${user_id}, "工具","https://www.12306.cn/index/","中国铁路12306", "#icon-a-12306","",""),
            (${user_id}, "工具","https://www.ctrip.com/","携程", "#icon-xiecheng","",""),
            (${user_id}, "工具","https://www.fliggy.com/?ttid=seo.000000580&seoType=origin","飞猪旅行", "#icon-feizhu","",""),
            (${user_id}, "工具","https://map.baidu.com/","百度地图", "#icon-baiduditu","",""),
            (${user_id}, "视频","http://www.bilibili.com/","哔哩哔哩", "#icon-bilibili1","",""),
            (${user_id}, "视频","https://www.douyin.com/","抖音", "#icon-douyin","",""),
            (${user_id}, "视频","https://www.kuaishou.com/new-reco","快手", "#icon-kuaishou","",""),
            (${user_id}, "视频","https://v.qq.com/","腾讯视频", "#icon-tengxunshipin","",""),
            (${user_id}, 
            "视频","https://www.iqiyi.com/","爱奇艺", "#icon-aiqiyi","",""),
            (${user_id}, "视频","https://youku.com/channel/webhome","优酷", "#icon-youku","",""),
            (${user_id}, "视频","https://youku.com/channel/webhome","虎牙直播", "#icon-huya","",""),
            (${user_id}, "视频","https://youku.com/channel/webhome","斗鱼直播", "#icon-douyu","",""),
            (${user_id}, "视频","https://www.youtube.com/","YouTube", "#icon-youtube","",""),
            (${user_id}, "程序员","https://github.com/","Github", "#icon-GitHub","",""),
            (${user_id}, "程序员","https://www.cnki.net/","CSDN", "#icon-csdn","",""),
            (${user_id}, "程序员","https://gitee.com/","Gitee", "#icon-gitee","",""),
            (${user_id}, "程序员","https://juejin.cn/","稀土掘金", "#icon-juejin","",""),
            (${user_id}, "程序员","https://www.runoob.com/","菜鸟教程", "#icon-logo","",""),
            (${user_id}, "程序员","https://www.w3school.com.cn/index.html","w3school教程", "#icon-a-w3schoolzaixianjiaocheng1","",""),
            (${user_id}, "程序员","https://www.cnblogs.com/","博客园", "#icon-cnblogs","",""),
            (${user_id}, "程序员","https://leetcode.cn/","力扣", "#icon-leetcode","",""),
            (${user_id}, "程序员","https://developer.mozilla.org/zh-CN/","MDN", "#icon-mdn","",""),
            (${user_id}, "程序员","https://www.java.com/zh-CN/","Java", "#icon-java","",""),
            (${user_id}, "程序员","https://www.python.org/","Python", "#icon-Python","",""),
            (${user_id}, "程序
            员","https://cloud.tencent.com/","腾讯云", "#icon-zonghenengyuanfuwupingtaikuangjiaicon-","",""),
            (${user_id}, "程序员","https://cn.aliyun.com/","阿里云", "#icon-Aliyun","",""),
            (${user_id}, "程序员","https://cn.vuejs.org/","Vue", "#icon-Vue","",""),
            (${user_id}, "程序员","https://react.docschina.org/","React", "#icon-react","",""),
            (${user_id}, "程序员","https://element.eleme.cn/#/zh-CN","Element", "#icon-elementui","",""),
            (${user_id}, "程序员","https://uniapp.dcloud.net.cn/","uniapp", "#icon-uni-app","",""),
            (${user_id}, "程序员","https://www.typescriptlang.org/","TypeScript", "#icon-typescript_","",""),
            (${user_id}, "程序员","https://www.oracle.com/cn/","Oracle", "#icon-Oracle","",""),
            (${user_id}, "购物","https://www.taobao.com/","淘宝", "#icon-taobao","",""),
            (${user_id}, "购物","https://www.jd.com/","京东", "#icon-jingdong","",""),
            (${user_id}, "购物","https://www.tmall.com/","天猫", "#icon-tianmao","",""),
            (${user_id}, "购物","https://www.suning.com/","苏宁易购", "#icon-suningyigou","",""),
            (${user_id}, "购物","https://www.1688.com/","阿里巴巴", "#icon-alibaba","",""),
            (${user_id}, "娱乐","https://m.weibo.cn/","微博", "#icon-weibo","",""),
            (${user_id}, "娱乐","https://www.zhihu.com/signin?next=%2F","知乎", "#icon-zhihu","",""),
            (${user_id}, "娱乐","https://www.toutiao.com/","今日头条", "#icon-jinritoutiao","",""),
            (${user_id}, "娱乐","https://www.douban.com/","豆瓣", "#icon-douban","",""),
            (${user_id}, "娱乐","https://music.163.com/","网易云音乐", "#icon-wangyiyun","",""),
            (${user_id}, "娱乐","https://y.qq.com/","QQ音乐", "#icon-QQyinle","",""),
            (${user_id}, "娱乐","https://www.kugou.com/","酷狗音乐", "#icon-a-kugou1","",""),
            (${user_id}, "娱乐","https://store.steampowered.com/","Steam", "#icon-steam","",""),
            (${user_id}, "娱乐","https://twitter.com/","Twitter", "#icon-twiter","","");
            `;
            connection.query(
                sql3, function (err, result) {
                    if (err) {
                        console.log('[SELECT ERROR]：', err.message);
                    }
                    // res.cookie("name",'zhangsan',{maxAge: 900000, httpOnly: true});
                    res.send(result);
                    console.log(result);
                })
        })
})

// 更新 修改账户信息
app.get('/update', function (req, res) {
    let username = req.query.username
    let password = req.query.password
    // console.log(username);
    // console.log(password);
    // `UPDATE users  SET password='5000'  WHERE username='菜鸟教程';`
    let sql = `UPDATE users  SET password="${password}"  WHERE username="${username}";`
    connection.query(
        sql, function (err, result) {
            if (err) {
                console.log('[UPDATE ERROR]：', err.message);
            }
            res.send(result);
        })
})


// 获取该用户所有的备忘录
app.get('/getMemList', function (req, res) {
    let user_id = req.query.user_id
    let sql = `select * from memList where user_id = ${user_id};`
    // console.log(user_id);
    connection.query(
        sql, function (err, result) {
            if (err) {
                console.log('[SELECT ERROR]：', err.message);
            }
            // console.log(result);
            res.send(result);
        })
})


// 修改备忘录
app.get('/updateOneMem', function (req, res) {
    let user_id = req.query.user_id
    let text_id = req.query.text_id
    let text = req.query.text
    let sql = `UPDATE memList  SET text="${text}"  WHERE user_id="${user_id}" AND text_id ="${text_id}";`
    // console.log(user_id);
    connection.query(
        sql, function (err, result) {
            if (err) {
                console.log('[SELECT ERROR]：', err.message);
            }
            // console.log(result);
            res.send(result);
        })
})

// 新增备忘录
app.get('/addOneMem', function (req, res) {
    let user_id = req.query.user_id
    let text = req.query.text
    let sql = `INSERT INTO memList (user_id, text, createTime, updateTime)
    VALUES (${user_id}, "${text}", CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP())`;
    // console.log(user_id);
    connection.query(
        sql, function (err, result) {
            if (err) {
                console.log('[INSERT ERROR]：', err.message);
            }
            // console.log(result);
            res.send(result);
        })
})

// 获取一条备忘录
// app.get('/getOneMem', function (req, res) {
//     let user_id = req.query.user_id
//     let text_id = req.query.text_id
//     let sql = `select * from memList where user_id = ${user_id} and text_id ="${text_id}";`
//     console.log(user_id);
//     console.log(text_id);
//     connection.query(
//         sql, function (err, result) {
//             if (err) {
//                 console.log('[SELECT ERROR]：', err.message);
//             }
//             // console.log(result);
//             res.send(result);
//         })
// })

// 删除一条备忘录
app.get('/deleteOneMem', function (req, res) {
    let user_id = req.query.user_id
    let text_id = req.query.text_id
    let sql = `delete from memList where user_id = ${user_id} and text_id ="${text_id}";`
    connection.query(
        sql, function (err, result) {
            if (err) {
                console.log('[DELETE ERROR]：', err.message);
            }
            // console.log(result);
            res.send(result);
        })
})


// 获取一个用户下的所有自定义搜索引擎
app.get('/getEngineList', function (req, res) {
    let user_id = req.query.user_id
    let sql = `select * from engineList where user_id = ${user_id};`
    // console.log(user_id);
    connection.query(
        sql, function (err, result) {
            if (err) {
                console.log('[SELECT ERROR]：', err.message);
            }
            // console.log(result);
            res.send(result);
        })
})

// 新增自定义搜索引擎
app.get('/addEngine', function (req, res) {
    let user_id = req.query.user_id;
    let engineName = req.query.engineName;
    let engineUrl = req.query.engineUrl;
    let sql = `INSERT INTO engineList (user_id,engineName, engineUrl) 
    VALUES (${user_id}, "${engineName}", "${engineUrl}");`;
    // console.log(user_id);
    connection.query(
        sql, function (err, result) {
            if (err) {
                console.log('[INSERT ERROR]：', err.message);
            }
            // console.log(result);
            res.send(result);
        })
})

// 获取一条自定义搜索引擎
app.get('/getOneEngineList', function (req, res) {
    let user_id = req.query.user_id
    let sql = `select * from engineList where user_id = ${user_id} and engine_id ="${engine_id}";`
    // console.log(user_id);
    connection.query(
        sql, function (err, result) {
            if (err) {
                console.log('[SELECT ERROR]：', err.message);
            }
            // console.log(result);
            res.send(result);
        })
})

// 删除一条引擎
app.get('/deleteOneMem', function (req, res) {
    let user_id = req.query.user_id;
    let engine_id = req.query.text_id;
    let sql = `delete from engineList where user_id = ${user_id} and engine_id ="${engine_id}";`
    connection.query(
        sql, function (err, result) {
            if (err) {
                console.log('[DELETE ERROR]：', err.message);
            }
            // console.log(result);
            res.send(result);
        })
})

// // 删除所有分页
// app.get('/deleteNavArray', function (req, res) {
//     let user_id = req.query.user_id
//     let sql = `delete from navArray where user_id = ${user_id} ;`
//     connection.query(
//         sql, function (err, result) {
//             if (err) {
//                 console.log('[DELETE ERROR]：', err.message);
//             }
//             // console.log(result);
//             res.send(result);
//         })
// })

// 删除一条分页
app.get('/deleteOneNavArray', function (req, res) {
    let user_id = req.query.user_id
    let nav_id = req.query.nav_id
    let sql = `delete from navArray where user_id = ${user_id} and nav_id ="${nav_id}";`
    connection.query(
        sql, function (err, result) {
            if (err) {
                console.log('[DELETE ERROR]：', err.message);
            }
            // console.log(result);
            res.send(result);
        })
})

// 获取一个用户下的所有自定义搜索引擎
app.get('/getNavArray', function (req, res) {
    let user_id = req.query.user_id
    let sql = `select * from navArray where user_id = ${user_id};`
    connection.query(
        sql, function (err, result) {
            if (err) {
                console.log('[SELECT ERROR]：', err.message);
            }
            // console.log(result);
            res.send(result);
        })
})

// 新增一条分页
app.get('/addOneNav', function (req, res) {
    let user_id = req.query.user_id;
    let page = req.query.page;
    let icon = req.query.icon;
    let sql = `INSERT INTO navArray (user_id,page, icon) 
    VALUES (${user_id}, "${page}", "${icon}");`;
    // console.log(user_id);
    connection.query(
        sql, function (err, result) {
            if (err) {
                console.log('[INSERT ERROR]：', err.message);
            }
            // console.log(result);
            res.send(result);
        })
})


// 获取一个用户下的所有快捷图标
app.get('/getAllLink', function (req, res) {
    let user_id = req.query.user_id
    let sql = `select * from linkTotalArray where user_id = ${user_id};`
    // console.log(user_id);
    connection.query(
        sql, function (err, result) {
            if (err) {
                console.log('[SELECT ERROR]：', err.message);
            }
            // console.log(result);
            res.send(result);
        })
})

// 删除一个快捷图标
app.get('/deleteOneLink', function (req, res) {
    let user_id = req.query.user_id
    let link_id = req.query.link_id
    let sql = `delete from linkTotalArray where user_id = ${user_id} and link_id ="${link_id}";`
    connection.query(
        sql, function (err, result) {
            if (err) {
                console.log('[DELETE ERROR]：', err.message);
            }
            // console.log(result);
            res.send(result);
        })
})

// 删除该用户下所有快捷图标
app.get('/deleteAllLink', function (req, res) {
    let user_id = req.query.user_id
    let sql = `delete from linkTotalArray where user_id = ${user_id} ;`
    connection.query(
        sql, function (err, result) {
            if (err) {
                console.log('[DELETE ERROR]：', err.message);
            }
            // console.log(result);
            res.send(result);
        })
})

// 删除一个分页下所有图标
app.get('/deleteOneNavLink', function (req, res) {
    let user_id = req.query.user_id
    let page = req.query.page
    let sql = `delete from linkTotalArray where user_id = ${user_id} and page ="${page}";`
    connection.query(
        sql, function (err, result) {
            if (err) {
                console.log('[DELETE ERROR]：', err.message);
            }
            // console.log(result);
            res.send(result);
        })
})


// 修改图标(文字图标)
app.get('/updateOneLinkColor', function (req, res) {
    let user_id = req.query.user_id
    let link_id = req.query.link_id
    let page = req.query.page
    let url = req.query.url
    let linkName = req.query.linkName
    let icon = req.query.icon
    let color = req.query.color
    let sql = `UPDATE memList  
    SET page="${page}",
    url="${url}",
    linkName="${linkName}",
    icon="${icon}",
    color="${color}",
    WHERE user_id="${user_id}" AND text_id ="${link_id}";`
    // console.log(user_id);
    connection.query(
        sql, function (err, result) {
            if (err) {
                console.log('[SELECT ERROR]：', err.message);
            }
            console.log(result);
            res.send(result);
        })
})

// 修改图标(自定义图片)
app.get('/updateOneLinkImg', function (req, res) {
    let user_id = req.query.user_id
    let page = req.query.page
    let url = req.query.url
    let linkName = req.query.linkName
    let icon = req.query.icon
    let color = req.query.color
    let img = req.query.img
    let sql = `UPDATE memList  
    SET page="${page}",
    url="${url}",
    linkName="${linkName}",
    icon="${icon}",
    color="${color}",
    img="${img}",
    WHERE user_id="${user_id}" AND text_id ="${link_id}";`
    // console.log(user_id);
    connection.query(
        sql, function (err, result) {
            if (err) {
                console.log('[SELECT ERROR]：', err.message);
            }
            console.log(result);
            res.send(result);
        })
})

// // 重新输入所有图标
// app.post('/inputAllLink', function (req, res) {
//     let arr = Object.keys(req.query.locList);
//     let user_id = req.query.user_id
//     let page = ""
//     let url = ""
//     let linkName = "" 
//     let icon = ""
//     let color = ""
//     let img = ""
//     let sql = `INSERT INTO linkTotalArray (user_id,page,url,linkName,icon,color,img)
//     VALUES `
//     for (let i = 0; i < arr.length; i++) {
//         page = req.query.locList[i].page
//         url = req.query.locList[i].url
//         linkName = req.query.locList[i].linkName
//         icon = req.query.locList[i].icon
//         color = req.query.locList[i].color
//         img = req.query.locList[i].img
//         // console.log(req.query.locList[i]);
//         if (i == arr.length - 1) {
//             sql += `(${user_id}, "${page}","${url}","${linkName}", "${icon}","${color}","${img}");`
//             // console.log(sql);
//             return;
//         }
//         sql += `(${user_id}, "${page}","${url}","${linkName}", "${icon}","${color}","${img}"),`
//     }
//      connection.query(
//         sql, function (err, result) {
//             if (err) {
//                 console.log('[INSERT ERROR]：', err.message);
//             }
//             // console.log(result);
//             res.send(result);
//         })
// })

// 重新输入所有图标
// app.post('/inputAllLink', function (req, res) {
//     let arr = Object.keys(req.query.locList);
//     let user_id = req.query.user_id
//     let page = ""
//     let url = ""
//     let linkName = "" 
//     let icon = ""
//     let color = ""
//     let img = ""
//     let sql = `UPDATE INTO linkTotalArray (user_id,page,url,linkName,icon,color,img)
//     VALUES `
//     for (let i = 0; i < arr.length; i++) {
//         page = req.query.locList[i].page
//         url = req.query.locList[i].url
//         linkName = req.query.locList[i].linkName
//         icon = req.query.locList[i].icon
//         color = req.query.locList[i].color
//         img = req.query.locList[i].img
//         // console.log(req.query.locList[i]);
//         if (i == arr.length - 1) {
//             sql += `(${user_id}, "${page}","${url}","${linkName}", "${icon}","${color}","${img}");`
//             // console.log(sql);
//             return;
//         }
//         sql += `(${user_id}, "${page}","${url}","${linkName}", "${icon}","${color}","${img}"),`
//     }
//      connection.query(
//         sql, function (err, result) {
//             if (err) {
//                 console.log('[INSERT ERROR]：', err.message);
//             }
//             // console.log(result);
//             res.send(result);
//         })
// })


// 新增一个图标(文字图标)
app.get('/addOneLinkColor', function (req, res) {
    // console.log(req);
    let user_id = req.query.user_id
    let page = req.query.page
    let url = req.query.url
    let linkName = req.query.linkName
    let color = req.query.color
    let sql = `INSERT INTO linkTotalArray (user_id,page,url,linkName,color)
    VALUES (${user_id}, "${page}", "${url}", "${linkName}", "${color}");`;
    console.log(color);
    connection.query(
        sql, function (err, result) {
            if (err) {
                console.log('[INSERT ERROR]：', err.message);
            }
            // console.log(result);
            res.send(result);
        })
})

// 新增一个图标(自定义图片)
app.post('/addOneLinkImg', function (req, res) {
    console.log(req.query);
    let user_id = req.query.user_id
    let page = req.query.page
    let url = req.query.url
    let linkName = req.query.linkName
    let img = req.query.img
    console.log(img);
    let sql = 
    `INSERT INTO linkTotalArray (user_id,page,url,linkName,img) VALUES (${user_id}, "${page}", "${url}", "${linkName}", "${img}");`;
    // console.log(user_id);
    connection.query(
        sql, function (err, result) {
            if (err) {
                console.log('[INSERT ERROR]：', err.message);
            }
            // console.log(result);
            res.send(result);
        })
})




// 监听端口
app.listen(8080, () => {
    console.log("服务已经启动，8080 端口监听中...");
})